/*********************************************************************************
 *      Copyright:  (C) 2021 litupei<lismilepei@163.com>
 *                  All rights reserved.
 *
 *       Filename:  prj_sqlite.h
 *    Description:  This file is prj_sqlite.h
 *                 
 *        Version:  1.0.0(2021年08月04日)
 *         Author:  litupei <lismilepei@163.com>
 *      ChangeLog:  1, Release initial version on "2021年08月04日 12时23分47秒"
 *                 
 ********************************************************************************/

#include "prj_sqlite.h"
//创建一个表格
int create_table(sqlite3 *db)
{
	char	*sql;
	char	*errmsg;

	sql = "CREATE TABLE Temperature(ID INT PRIMARY KEY NOT NULL, temp FLOAT, TIME CHAR(32));";
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
   	{
		return 0;
   	}
	return 1;
}

//增加表格的数据
int insert_data(sqlite3 *db, char *time_now, float temp, int id)
{
	char	sql[128];
	char	*errmsg;

	memset(sql, 0, sizeof(sql));
	snprintf(sql, sizeof(sql), "insert into Temperature values(%d,%.2f,'%s');", id, temp, time_now);

	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("the data insert failture: %s! \n", errmsg);
        	return -1;
	}
	printf("the data insert successfully! \n");

	return 0;
}


//回调函数，返回查询数据的结果
static int MAXID_callback(void *data, int argc, char **argv, char **azColName)
{
	int	*id;
    
    	id = (int *)data;
    
	if( !argv[0] )
	{
		printf("table is void! \n");
        	*id = 1;
	}

	else
    	{
        	*id = atoi(argv[0]) + 1;
    	}

    	return 0;
}

//删除表格的数据
int delete_data(sqlite3 *db, int id)
{
	char	sql[128];
	char	*errmsg;

	snprintf(sql, sizeof(sql), "delete from Temperature where id = %d;",id);

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		printf("the data delete failture: %s\n",errmsg);
	}
	printf("the data delete successfully! \n");

	return 0;

}


//修改表格的数据
int updata_data(sqlite3 *db)
{
	char    *errmsg;
	char    sql[128] = {0};

	printf("Input the sqlite3 command you want exec:\n");
	fgets(sql,sizeof(sql),stdin);

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		printf("updata the data failture : %s\n",errmsg);
	}
	printf("updata the data successfully! \n");

	return 0;
}


//查找表格的数据
int select_data(sqlite3 *db)
{
	char	*errmsg;
	char	*sql;

	sql =  "select * from Temperature;";
	if(sqlite3_exec(db,sql,callback,NULL,&errmsg) != SQLITE_OK)
	{
		printf("%s\n",errmsg);
	}

	return 0;
}

//回调函数，查询数据的数据
static int callback(void *data, int argc, char **argv, char **azColName)
{
	int     i;

	//fprintf(stderr, "%s: ", (const char*)data);

	for(i=0; i<argc; i++)
	{
		printf("%s = %s\n", azColName[i], argv[i]);
	}

	printf("\n");
	return 0;
}

//创建一个数据库
int create_sqlite(sqlite3 *db, char *sqlite_name)
{
	if(sqlite3_open(sqlite_name, &db) != SQLITE_OK)
	{
		printf("open the sqlite is failture : %s \n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return -1;
	}
	printf("openning the sqlite3 database named temp.db successfully! \n");

	if( !create_table(db) )
	{
		printf("the Temperature table is exit!\n");
	}

	else
	printf("the Sensor Temperature create successfully! \n");
	
	return 0;
}

int found_max_id(sqlite3 *db, int *id)
{
	char	sql[128];
	char	*errmsg;

	memset(sql, 0, sizeof(sql));

	snprintf(sql, sizeof(sql), "select max(ID) from Temperature;");
	if(sqlite3_exec(db, sql, MAXID_callback, (void *)id, &errmsg) != SQLITE_OK)
	{
		printf("found_max_id failture: %s! \n", errmsg);
    		return -1;
	}
	
	return 0;
}

